﻿using AutoMapper.Execution;
using Dapper;
using EFCore.BulkExtensions;
using Efcore_Test.Data;
using Efcore_Test.Entities;
using Efcore_Test.Services.Dtos;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Caching.Distributed;
using MySqlConnector;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;
using Volo.Abp;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories.EntityFrameworkCore;
using Volo.Abp.EntityFrameworkCore;
using Volo.Abp.Uow;

namespace Efcore_Test.Repositorys
{
    /// <summary>
    /// 仓储”((Repository)
    ///     仓储负责按照要求从数据库中读取数据以及把领域服务（三层架构中的领域服务）修改的数据保存回数据库。
    /// </summary>
    [Dependency(ServiceLifetime.Transient)]
    public class CommentRepository : EfCoreRepository<Efcore_TestDbContext, Comment, long>, ICommentRepository
    {
        /// <summary>
        /// 默认存储在MemoryDistributedCache Microsoft.Extensions.Caching.Memory
        /// 缓存在本地应用进程内的内存中
        /// </summary>
        public IDistributedCache distributedCache { get; set; }


        public CommentRepository(IDbContextProvider<Efcore_TestDbContext> dbContextProvider) : base(dbContextProvider)
        {

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="autoSave"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public override Task InsertManyAsync(IEnumerable<Comment> entities, bool autoSave = false, CancellationToken cancellationToken = default)
        {
            return base.InsertManyAsync(new List<Comment>
            { new Comment { ArticleId = 1 ,Message = "1234"},
                new Comment { ArticleId = 1, Message = "1234" },
                new Comment { ArticleId = 1, Message = "1234" } }, autoSave, cancellationToken);
        }

        public override async Task<Comment> InsertAsync(Comment entity, bool autoSave = false, CancellationToken cancellationToken = default)
        {
            var db = await GetDbContextAsync();
            //var data = new List<Comment>
            //{ new Comment { ArticleId = 1 ,Message = "1234"},
            //    new Comment { ArticleId = 1, Message = "1234" },
            //    new Comment { ArticleId = 1, Message = "1234" } };
            //db.comments.AddRange(data);
            //批量添加
            //await db.BulkInsertAsync(data);
            //return await base.InsertAsync(entity, autoSave, cancellationToken);
            await db.comments.Where(x => x.Id > 5).ExecuteUpdateAsync(set => set.SetProperty(u => u.Message, "哈哈"));
            return await Task.FromResult(new Comment());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="includeDetails"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public override async Task<Comment?> FindAsync(long id, bool includeDetails = true, CancellationToken cancellationToken = default)
        {

            //User user = new User() { Name = "xxx" };
            //Leave leave = new Leave() { Requester = user ,Remarksget = "xxxx"};
            //(await GetDbContextAsync()).leaves.Add(leave);
            //await (await GetDbContextAsync()).SaveChangesAsync();  //efcore 会自动寻找关系添加数据

            //1、
            //DataReader:分批从数据库服务器读取 1、数据内存占用小、DB连接用时间长;
            //DataTable:把所有数据都一次性从数据库服务器都加载到客户端内存中。内存占用大，节省DB连接

            //2、
            //一次性加载数据到内存:用lQueryable的ToArray()、ToArrayAsync()、ToList().ToListAsync()等方法
            //等ToArray()执行完毕，再断服务器试一下

            //3、
            //场景3:多个lQueryable的遍历嵌套。很多数据库的ADO.NET Core Provider是不支持多个DataReader同时执行的。
            //把连接字符串中的MultipleActiveResultSets=true删掉，其他数据库不支持这个，
            var db = (await GetDbContextAsync());
            foreach (var item in db.orders)
            {
                Console.WriteLine(item.Id);
                foreach (var it in db.teachers)
                {
                    Console.WriteLine(it.Id);
                }
            }

            //4、
            ////遍历：DataRender方式从数据库读取数据，节省客户端内存、
            //如果处理慢，会长时间占用连接
            foreach (var item in (await GetDbContextAsync()).orders)
            {
                Console.WriteLine(item.Id);
            }
            return await (await GetDbContextAsync()).comments.Include(x => x.Article).FirstOrDefaultAsync(x => x.Id == id);
        }


        /// <summary>
        /// 1、字符串内插的方式会不会有SQL注入攻击漏洞吗?查看一下执行的SQL语句吧
        /// 
        /// 2、字符串内插如果赋值给string变量，就是字符串拼接。字符串内插如果赋值给Formattablestring变量，编译器就会构造FormattableString对象。
        ///    打印FormaftableString的成员试试看
        ///   
        /// 3、ExecuteSqllnterpolatedAsync()的参数是FormattableString类型。因此ExecuteSqllnterpolatedAsync会进行参数化SQL的处理。
        /// 
        /// 4、除了ExecuteSqllnterpolated ()、ExecuteSqllnterpolatedAsync()，还有ExecuteSqlRaw()、ExecuteSqlRawAsync()也可以执行原生SQL语句，
        ///    但需要开发人员自己处理查询参数等了,因此不推荐使用。
        ///    
        /// 5、 般Linq操作就够了，尽量不用写原生SQL
        ///     1、非查询SQL用ExecuteSqllnterpolated ();
        ///     2、针对实体的SQL查询用FromSqllnterpolated().
        ///     3、复杂SQL查询用ADO.NET的方式或者Dapper等。
        /// 
        /// 6、实体状态
        ///    1、已添加(Added): Dbcontext正在跟踪此实体，但数据库中尚不存在该实体。
        ///    2、未改变(Unchanged):Dbcontext正在跟踪此实体，该实体存在于数据库中，其属性值和从数据库中读取到的值一致，未发生改变。
        ///    3、已修改(Modified):Dbcontext正在跟踪此实体，并存在于数据库中并且其部分或全部属性值已修改。
        ///    4、已删除(Deleted): Dbcontext正在跟踪此实体，并存在于数据库中但在下次调用 SaveChanges 时要从数据库中删除对应数据。
        ///    5、已分离(Detached):DbContext未跟踪该实体
        ///    
        /// 7、SAVECHANGES()的操作
        ///    已分离”和“本改变”的实体，!SaveChanges()忽略
        ///    已添加”的实体，SaveChanges()插入数据库
        ///    已修改”的实体，Savechanges()更新到数据库,
        ///    已删除”的实体，saveChanges()从数据库删除
        /// </summary>
        /// <returns></returns>
        public async Task test()
        {
            await foreach (var it in Rest())
            {
                Console.WriteLine(it);
            }

            #region 查询 原生sql语句 实体查询
            {
                //如果要执行的原生SQL是一个查询语句，并且查询的结果也能对应一个实体，就可以调用对应实体的Dbset的FromSqllnterpolated()方法来执行一个查询SQL语句，
                //同样使用字符串内插来传递参数。
                var users = (await GetDbContextAsync()).users.FromSqlInterpolated($@"select * from t_user where id ={1} and name = '{"你好啊"}'");
                users.OrderBy(x => Guid.NewGuid()).Skip(1).Take(10).ToList();
                foreach (var it in users)
                {
                    Console.WriteLine(it.Id);
                }
            }
            #endregion

            #region  非查询 原生 sql 语句
            {
                //除了ExecuteSqllnterpolated ()、ExecuteSqllnterpolatedAsync()，还有ExecuteSqlRaw()、ExecuteSqlRawAsync()也可以执行原生SQL语句，
                //但需要开发人员自己处理查询参数等了,因此不推荐使用。
                //防止sql注入攻击
                //自动化参数化查询
                //efcore 新版本方法
                //内插值语法
                var a = "";
                var b = "";
                var c = "";
                FormattableString formattableString = $@"select * from user where a = {a} and b = {b} adn  c = {c}";
                Console.WriteLine($"Fromat:{formattableString.Format}");
                Console.WriteLine($"参数:{string.Join(",", formattableString.GetArguments())}");
                await (await GetDbContextAsync()).Database.ExecuteSqlInterpolatedAsync(formattableString);

                //efcore 旧版本方法
                var ageParam = new MySqlParameter("@age", 18);
                await (await GetDbContextAsync()).Database.ExecuteSqlRawAsync("", ageParam);
            }
            #endregion

            #region 纯原生sql 复杂查询
            {
                //纯原生sql
                IDbConnection dbConnection = (await GetDbConnectionAsync());
                if (dbConnection.State != ConnectionState.Open)
                    dbConnection.Open();
                //原生sql语句
                using (var cmd = dbConnection.CreateCommand())
                {
                    cmd.CommandText = $"select * from user where a = {1} and b = {2} adn  c = {3}";
                    using (var ren = cmd.ExecuteReader())
                    {
                        while (ren.Read())
                        {
                            //
                        }
                    }
                }
                //dapper
                var data = dbConnection.Query<ArticleDto>($"select * from user where a = {1} and b = {2} adn  c = {3}");
            }
            #endregion

            #region  实体状态 查询验证
            {
                //结论
                //DbContext会根据跟踪的实体的状态，在SaveChanges()的时候，根据实体状态的不同，生成Update、DeleteInsert等SQL语句，
                //来把内存中实体的变化更新到数据库
                Efcore_TestDbContext dbContext = (await GetDbContextAsync());
                var data = dbContext.users.Take(3).ToList();
                var u1 = data[0];
                var u2 = data[1];
                var u3 = data[2];

                User u4 = new User();
                User u5 = new User();

                u1.Name += "xx";
                dbContext.Remove(u2);
                dbContext.users.Add(u4);

                //查询实体跟踪状态信息
                EntityEntry entity1 = dbContext.Entry(u1);
                EntityEntry entity2 = dbContext.Entry(u2);
                EntityEntry entity3 = dbContext.Entry(u3);
                EntityEntry entity4 = dbContext.Entry(u4);
                EntityEntry entity5 = dbContext.Entry(u5);

                //跟踪状态
                Console.WriteLine(entity1.State);
                Console.WriteLine(entity2.State);
                Console.WriteLine(entity3.State);
                Console.WriteLine(entity4.State);
                Console.WriteLine(entity5.State);

                //查询快照信息
                Console.WriteLine(entity5.DebugView.LongView);

                //不跟踪 节省内存占用
                var dataw = await (await GetDbContextAsync()).comments.Include(x => x.Article).FirstOrDefaultAsync(x => x.Id == 1);
                Console.WriteLine((dbContext.Entry(dataw)).State);

                //直接修改，不用查询 
                User user = new User() { Id = 1, Name = "123" };
                EntityEntry entityEntry = dbContext.Entry(user);
                entityEntry.Property("Name").IsModified = true;
                dbContext.SaveChanges();
            }
            #endregion

            #region 全局过滤查询条件
            {
                var db = await GetDbContextAsync();
                db.users.Where(x => x.Id == 1);
                //忽略全局过滤条件
                db.users.IgnoreQueryFilters().Where(x => x.Id == 1);
            }
            #endregion

            #region 乐观锁
            {
                var dbcontext = await GetDbContextAsync();
                //Func<Comment, bool> func = x => x.Id > 6;
                //var data = dbcontext.comments.Where(func);
                var comment = await FindAsync(1);
                comment.Version = comment.Version + 1;
                comment.Message = "newName";
                try
                {
                    await UpdateAsync(comment);
                }
                catch (DbUpdateConcurrencyException ex)
                {
                    //获取他人修改后新的值  从数据库中查询
                    var entry1 = ex.Entries.First();
                    var newValue = entry1.GetDatabaseValues()?.GetValue<string>("Version");
                    throw new UserFriendlyException("数据已被其他人修改，请刷新后重试");
                }
            }
            #endregion

            #region 表达式目录树 
            {
                var db = await GetDbContextAsync();
                db.users.Select(x => new { x.Name, x.Id });
            }
            #endregion  
        }

        public async Task<IEnumerable<object[]>> CreatePropertyExpression<T>(string[] propertyNames) where T : class
        {
            var p = Expression.Parameter(typeof(T), "p");
            List<Expression> expressions = new List<Expression>();
            foreach (var property in propertyNames)
            {
                //MemberExpression memberExpression =   Expression.MakeMemberAccess(p, typeof(T).GetProperty(property));
                MemberExpression memberExpression = Expression.Property(p, property);
                UnaryExpression converted = Expression.Convert(memberExpression, typeof(object));
                expressions.Add(converted);
            }
            NewArrayExpression newArry = Expression.NewArrayInit(typeof(object), expressions.ToArray());

            Expression<Func<T, Object[]>> selct = Expression.Lambda<Func<T, Object[]>>(newArry, p);
            var db = await GetDbContextAsync();
            var t = db.Set<T>().Select(selct).ToList();
            return t;
        }

        /// <summary>
        /// 解决 async 和 yield 不能同时使用的问题
        /// </summary>
        /// <returns></returns>
        public async IAsyncEnumerable<string> Rest()
        {
            //await (await GetDbContextAsync()).comments.Include(x => x.Article).FirstOrDefaultAsync(x => x.Id == 1);
            yield return "123";
            yield return "123";
            yield return "123";
        }

        /// <summary>
        /// 解决 async 和 yield 不能同时使用的问题
        /// </summary>
        /// <returns></returns>
        //public async Task<IEnumerable<string>> Restw()
        //{
        //    yield return "123";
        //    yield return "123";
        //    yield return "123";
        //}
        //public IEnumerable<string> Rests()
        //{
        //    yield return "123";
        //    yield return "123";
        //    yield return "123";
        //}
    }
}
